<?php

class GenealogyController extends Controller
{
	public function actionIndex()
	{
		$this->logAction();
		$this->render('index');
	}
	
	public function actionVolumes()
	{
		$this->logAction();
		$this->render('volumes');
	}
	
	public function getMember( $member_id ) 
	{
		$connection = Yii::app()->db;
   
		$sql = "SELECT member_id, first_name, last_name, middle_name,
		               main_address, email, mobile_no, sponsor_id  
		          FROM gn_members 
				 WHERE member_id=$member_id";
				 
		$command = $connection->createCommand($sql);
		$dataReader = $command->query();
		
		$data = NULL;
		
		foreach( $dataReader as $row ) { 
		   $data = $row; 
        }
		
		return $data;
    }
	
	public function getChildren( $member_id ) 
	{
		$connection = Yii::app()->db;
 
		$sql = "SELECT member_id, first_name, last_name, position, sponsor_id 
		          FROM gn_members 
				 WHERE sponsor_id=$member_id";
				 
		$command = $connection->createCommand($sql);
		$dataReader = $command->query();
		
		$members = array();
		
		foreach( $dataReader as $row ) {
		   $members[$row['position']] = $row;
		}
		
		return $members;
	}
	
	public function getDownlines( $parent_id, &$downlines ) 
	{
		$connection = Yii::app()->db;
 
		$sql = "SELECT member_id, first_name, last_name, position, sponsor_id 
		          FROM gn_members 
				 WHERE sponsor_id=$parent_id";
		$command = $connection->createCommand($sql);
		$dataReader = $command->query();
		
		foreach( $dataReader as $row ) {
		   array_push( $downlines, $row );
		   $this->getDownlines( $row['member_id'], $downlines );
		}
	}
	
	public function getVolumeDownlines( $parent_id, $year, $week_no, &$downlines, &$total_points ) 
	{
		$connection = Yii::app()->db;
 
		$sql = "SELECT member_id, first_name, last_name, position, sponsor_id 
		          FROM gn_members 
				 WHERE sponsor_id=$parent_id";
				 
		$command = $connection->createCommand($sql);
		$dataReader = $command->query();
		
		foreach( $dataReader as $row ) {
		   $bv_personal = $this->getMemberPersonalBV( $row['member_id'], $year, $week_no);
		   
		   if ( $bv_personal )
		   {
		   	  $row['bv_personal'] = $bv_personal;
			  $total_points += $bv_personal;
		   	  array_push( $downlines, $row );
		   }
		   
		   $this->getVolumeDownlines( $row['member_id'], $year, $week_no, $downlines, $total_points );
		}
	}
	
	public function getMemberPersonalBV($member_id=0, $year, $week_no )
	{
		$connection = Yii::app()->db;
		
		$sql = "SELECT bv_personal 
		          FROM gn_payouts  
		         WHERE member_id=$member_id
		           AND week_no=$week_no
		           AND year=$year";
					  
		$command = $connection->createCommand($sql);
	    $data = $command->query()->read();   
	    
	    if ( isset($data['bv_personal']) )
	       return $data['bv_personal'];
        else
           return 0;
	}
	
	public function array_implode( $glue, $separator, $array ) {
		if ( ! is_array( $array ) ) return $array;
		$string = array();
		foreach ( $array as $key => $val ) {
			if ( is_array( $val ) )
				$val = implode( ',', $val );
			$string[] = "{$key}{$glue}{$val}";
		   
		}
		
		return implode( $separator, $string );
	}
	
	public function logAction()
	{
		$datetime = date("Y-m-d H:i:s");
		$user = Yii::app()->user->id;
		$action =  $this->id . '/' . $this->action->id;
		$data   = $this->array_implode( "=", "::", $_GET );
		$data  .= $this->array_implode( "=", "::", $_POST );
		
		$sql = "INSERT INTO gn_audit_trail SET
		               datetime='$datetime', 
					   user='$user',
					   action='$action',
					   data='$data'";
					   
		$connection = Yii::app()->db;
		$command = $connection->createCommand($sql);
		$dataReader = $command->query();
		
	}
	

	// Uncomment the following methods and override them if needed
	/*
	public function filters()
	{
		// return the filter configuration for this controller, e.g.:
		return array(
			'inlineFilterName',
			array(
				'class'=>'path.to.FilterClass',
				'propertyName'=>'propertyValue',
			),
		);
	}

	public function actions()
	{
		// return external action classes, e.g.:
		return array(
			'action1'=>'path.to.ActionClass',
			'action2'=>array(
				'class'=>'path.to.AnotherActionClass',
				'propertyName'=>'propertyValue',
			),
		);
	}
	*/
	
	public function actionExcelGenealogy()
	{
		$parent = $this->getMember( $_GET['id'] );
		$parent_header  = '['.$parent['member_id'].'] '.$parent['last_name'].', '.$parent['first_name'];
		$parent_header .= ' '.$parent['middle_name'];
		
		$l_downlines = array();
		$r_downlines = array();
		
		$children = $this->getChildren( $_GET['id'] );
	
		if ( isset($children['left']) ) {
		  $data = $children['left'];	
		  array_push( $l_downlines, $data );
		  $this->getDownlines( $data['member_id'], $l_downlines );
		}
		
		if ( isset($children['right']) ) {
		  $data = $children['right'];	
		  array_push( $r_downlines, $data );
		  $this->getDownlines( $data['member_id'], $r_downlines );
		}
	
		$l_total = count($l_downlines);
		$r_total = count($r_downlines);
		
		$downlines = array ();
		
		if ( $l_total > 1 ) {
			foreach ( $l_downlines as $node ) {
			  
			  $data=array();	
			  $data['l_id'] = $node['member_id'];
			  $data['l_first_name'] = $node['first_name'];
			  $data['l_last_name']  = $node['last_name'];
			  $data['l_sponsor_id'] = $node['sponsor_id'];
			  $data['div'] = "";
			  $data['r_id'] ="";
			  $data['r_first_name'] = "";
			  $data['r_last_name']  = "";
			  $data['r_sponsor_id'] = "";
			  
			  array_push( $downlines, $data );
			}
		}
		
		
		if ( $r_total > 1 ) {
			$i=0;
			foreach ( $r_downlines as $node ) {
			  
			  if ( isset($downlines[$i]) ) {
			      $downlines[$i]['r_id'] = $node['member_id'];
			      $downlines[$i]['r_first_name'] = $node['first_name'];
			      $downlines[$i]['r_last_name']  = $node['last_name'];
			      $downlines[$i]['r_sponsor_id'] = $node['sponsor_id'];
			  }
			  else
			  {
				  $data=array();
				  $data['l_id'] ="";
				  $data['l_first_name'] = "";
				  $data['l_last_name']  = "";
				  $data['l_sponsor_id'] = "";
				  $data['div'] = "";
				  $data['r_id'] = $node['member_id'];
				  $data['r_first_name'] = $node['first_name'];
				  $data['r_last_name']  = $node['last_name'];
				  $data['r_sponsor_id'] = $node['sponsor_id'];
				  array_push( $downlines, $data );
			  }
			  
			  $i++;
			  
			}
		}
		
		$data=array();
	    $data['l_id'] ="";
	    $data['l_first_name'] = "";
	    $data['l_last_name']  = "";
	    $data['l_sponsor_id'] = "";
	    $data['div'] = $parent_header;
	    $data['r_id'] = "";
	    $data['r_first_name'] = "";
	    $data['r_last_name']  = "";
	    $data['r_sponsor_id'] = "";
		
		array_unshift( $downlines, $data );
		
		//print_r ( $downlines );
		
		$dataProvider = new CArrayDataProvider($downlines);
	    $this->toExcel($dataProvider); 
		
	}
	
	public function behaviors(){
	
	    return array(
            'eexcelview'=>array(
                'class'=>'ext.eexcelview.EExcelBehavior',
            ),
        );
	}
}